package com.aha.dataanalyzeandview.utils;


import com.aha.dataanalyzeandview.exception.RdbAdapterException;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


@Slf4j
public class MyJdbcUtils {


    /**
     * 执行查询语句
     *
     * @param jdbcUrl  hive jdbc url
     * @param dbName   数据库名
     * @param username 用户名
     * @param password 密码
     * @param adminStatement 是不是使用超级管理员权限， hive 专用。其他数据源类型请使用 null
     * @param sql      sql
     * @return
     * @throws Exception
     */
    public static List<List<String>> executeQuery(String jdbcUrl, String driverName, String dbName, String username,
                                                  String password, String adminStatement, String sql,
                                                  int fieldSize) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        String url = String.format("%s/%s", jdbcUrl, dbName);
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            log.error("加载驱动错误：{}", e.getMessage());
            closeConn(conn, ps, resultSet);
            throw new RdbAdapterException("加载驱动错误：" + e.getMessage(), conn, ps);
        }
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            log.error("获取连接错误：{}", e.getMessage());
            closeConn(conn, ps, resultSet);
            throw new RdbAdapterException("获取连接错误：" + e.getMessage(), conn, ps);
        }
        try {
            if (adminStatement != null) {
                // 声明 admin  是超级管理员 , set role admin
                ps = conn.prepareStatement(adminStatement);
                ps.execute();
            }
            ps = conn.prepareStatement(sql);
        } catch (SQLException e) {
            log.error("执行SQL错误,准备sql异常：{}", e.getMessage());
            if (e.getMessage().contains("admin doesn't belong to role admin")) {
                throw new RuntimeException("hiveServer2没有配置权限！");
            } else {
                closeConn(conn, ps, resultSet);
                throw new RdbAdapterException("执行SQL错误：" + e.getMessage(), conn, ps);
            }
        }
        try {
            resultSet = ps.executeQuery();
        } catch (SQLException e) {
            log.error("执行SQL错误，获取执行结果异常：{}", e.getMessage());
            closeConn(conn, ps, resultSet);
            throw new RdbAdapterException("执行SQL错误：" + e.getMessage(), conn, ps);
        }

        List<List<String>> res = new ArrayList<>();
        while (true) {
            try {
                if (!resultSet.next()) {break;}
            } catch (SQLException e) {
                log.error("执行SQL错误，查询sql查询结果异常：{}", e.getMessage());
                closeConn(conn,ps,resultSet);
                throw new RdbAdapterException("执行SQL错误：" + e.getMessage(),conn,ps);
            }
            List<String> row = new ArrayList<>();
            for (int i = 1; i < fieldSize + 1; i++) {
//                log.info(resultSet.getString(i));
                try {
                    row.add(resultSet.getString(i));
                } catch (SQLException e) {
                    log.error("执行SQL错误，打包结果异常：{}", e.getMessage());
                    closeConn(conn,ps,resultSet);
                    throw new RdbAdapterException("执行SQL错误：" + e.getMessage(),conn,ps);
                }
            }
            res.add(row);
        }
        closeConn(conn,ps,resultSet);
        return res;

    }





    /**
     * 关闭jdbc 连接
     *
     * @param conn
     * @param ps
     * @param rs
     */
    public static void closeConn(Connection conn, PreparedStatement ps, ResultSet rs) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                log.error("关闭连接错误：{}", e.getMessage());

            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("关闭连接错误：{}", e.getMessage());

            }
        }

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("关闭连接错误：{}", e.getMessage());

            }
        }
    }


    /**
     * 执行 sql
     *
     * @param username
     * @param password
     * @param jdbcUrl
     * @param dbName
     * @param sqlList
     * @return
     */
    public static boolean execute(String jdbcUrl, String driverName, String username, String password, String dbName, List<String> sqlList) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            log.error("加载驱动错误：{}", e.getMessage());
            closeConn(conn, ps, resultSet);
            throw new RdbAdapterException("加载驱动错误：" + e.getMessage(), conn, ps);
        }
        try {
            conn = DriverManager.getConnection(jdbcUrl, username, password);
        } catch (SQLException e) {
            log.error("获取连接错误：{}", e.getMessage());
            closeConn(conn, ps, resultSet);
            throw new RdbAdapterException("获取连接错误：" + e.getMessage(), conn, ps);
        }
        boolean executeRes = false;
        try {
            for (String sql : sqlList) {
                ps = conn.prepareStatement(sql);

                try {
                    ps.execute();
                    executeRes = true;
                } catch (SQLException e) {
                    e.printStackTrace();
                    log.error("执行SQL错误：{}", e.getMessage());
                    closeConn(conn, ps, resultSet);
                    throw new RdbAdapterException("执行SQL错误：" + e.getMessage(), conn, ps);
                }
            }
        } catch (SQLException e) {
            log.error("执行SQL错误：{}", e.getMessage());

            closeConn(conn, ps, resultSet);
            throw new RdbAdapterException("执行SQL错误：" + e.getMessage(), conn, ps);
        }

        closeConn(conn, ps, resultSet);
        return executeRes;
    }


//
//    public static void main(String[] args) {
//
//        String hiveJdbcUrl = "jdbc:hive2://192.168.10.132:10000";
//        String sql = String.format("show partitions %s", "yhwz");
////        List<List<String>> lists = executeQuery(jdbcUrl, "yhwz", "", "",
////                null, sql, 1);
////        if (lists.size() > 0) {
////            for (String s : lists.get(0)) {
////                System.out.println(s);
////            }
////        }
//
//
//        String delPartitionSql = String.format("alter table %s drop partition (%s)", "yhwz", "partdate=20200813");
//
//        boolean deletePartitionData = execute(jdbcUrl, "yhwz", "", "", delPartitionSql);
//        if(!deletePartitionData){
//            System.out.println("删除分区数据失败！");
//        }
//
//        List<List<String>> lists2 = executeQuery(jdbcUrl, "yhwz", "", "",
//                null, sql, 1);
//        if (lists2.size() > 0) {
//            for (String s : lists2.get(0)) {
//                System.out.println(s);
//            }
//        }else{
//            System.out.println("没有查到任何数据！");
//        }
//    }

}
